from sqlalchemy import Column, Integer, String, Boolean, DateTime, ForeignKey, Float, Text, Enum, JSON
from sqlalchemy.orm import relationship, declarative_base
from datetime import datetime
import uuid
import enum

Base = declarative_base()

class MediaType(enum.Enum):
    IMAGE = "image"
    VIDEO = "video"

class VehicleType(enum.Enum):
    CAR = "car"
    BUS = "bus"
    TRUCK = "truck"
    MOTORCYCLE = "motorcycle"
    BICYCLE = "bicycle"
    VAN = "van"
    TRAILER = "trailer"
    AMBULANCE = "ambulance"
    FIRE_TRUCK = "fire_truck"
    POLICE_CAR = "police_car"

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, index=True)
    password = Column(String(100))
    created_at = Column(DateTime, default=datetime.now)
    histories = relationship("History", back_populates="user", cascade="all, delete-orphan", passive_deletes=True)
    tasks = relationship("Task", back_populates="user", cascade="all, delete-orphan", passive_deletes=True)
    detection_records = relationship("DetectionRecord", back_populates="user", cascade="all, delete-orphan", passive_deletes=True)
    statistics = relationship("UserStatistics", back_populates="user", uselist=False, cascade="all, delete-orphan", passive_deletes=True)

class History(Base):
    __tablename__ = "histories"
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    input_file = Column(String(255))
    output_file = Column(String(255))
    pedestrian_count = Column(Integer)
    vehicle_count = Column(Integer)
    confidence_threshold = Column(Float)
    iou_threshold = Column(Float)
    max_detections = Column(Integer)
    media_type = Column(String(10), nullable=True)  # 'image' 或 'video'
    created_at = Column(DateTime, default=datetime.now)
    is_deleted = Column(Boolean, default=False)
    user = relationship("User", back_populates="histories")
    vehicle_details = relationship("VehicleDetail", back_populates="history", cascade="all, delete-orphan", passive_deletes=True)

class DetectionRecord(Base):
    __tablename__ = "detection_records"
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    image_path = Column(String(255))
    confidence_threshold = Column(Float)
    iou_threshold = Column(Float)
    max_detections = Column(Integer)
    detection_results = Column(JSON)
    media_type = Column(String(10), nullable=True)  # 'image' 或 'video'
    created_at = Column(DateTime, default=datetime.now)
    user = relationship("User", back_populates="detection_records")

# 任务状态常量
TASK_STATUS_PENDING = "pending"
TASK_STATUS_PROCESSING = "processing"
TASK_STATUS_COMPLETED = "completed"
TASK_STATUS_FAILED = "failed"

class Task(Base):
    __tablename__ = "tasks"
    id = Column(String(36), primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    input_file = Column(String(255))
    output_file = Column(String(255), nullable=True)
    status = Column(String(20))
    media_type = Column(String(10), nullable=True)  # 'image' 或 'video'
    confidence_threshold = Column(Float)
    iou_threshold = Column(Float)
    max_detections = Column(Integer)
    pedestrian_count = Column(Integer, default=0)
    vehicle_count = Column(Integer, default=0)
    error = Column(String(255), nullable=True)
    processing_time = Column(Float, nullable=True)  # 处理时间（秒）
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    user = relationship("User", back_populates="tasks")
    vehicle_details = relationship("VehicleDetail", back_populates="task", cascade="all, delete-orphan", passive_deletes=True)
    statistics = relationship("TaskStatistics", back_populates="task", uselist=False, cascade="all, delete-orphan", passive_deletes=True)
    details = relationship("DetectionDetail", back_populates="task", cascade="all, delete-orphan", passive_deletes=True)

class DetectionDetail(Base):
    __tablename__ = "detection_details"
    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(String(36), ForeignKey("tasks.id", ondelete="CASCADE"), nullable=False)
    object_type = Column(String(50))  # 检测对象类型（人、车等）
    confidence = Column(Float)  # 检测置信度
    frame_start = Column(Integer, nullable=True)  # 视频中的起始帧（如果是视频）
    frame_end = Column(Integer, nullable=True)  # 视频中的结束帧（如果是视频）
    created_at = Column(DateTime, default=datetime.now)
    
    task = relationship("Task", back_populates="details")

class VehicleDetail(Base):
    __tablename__ = "vehicle_details"
    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(String(36), ForeignKey("tasks.id", ondelete="CASCADE"), nullable=False)
    history_id = Column(Integer, ForeignKey("histories.id", ondelete="CASCADE"), nullable=True)
    vehicle_type = Column(String(20))  # 车辆类型
    count = Column(Integer)  # 数量
    created_at = Column(DateTime, default=datetime.now)
    task = relationship("Task", back_populates="vehicle_details")
    history = relationship("History", back_populates="vehicle_details")

class TaskStatistics(Base):
    __tablename__ = "task_statistics"
    id = Column(Integer, primary_key=True, index=True)
    task_id = Column(String(36), ForeignKey("tasks.id", ondelete="CASCADE"), nullable=False)
    total_objects = Column(Integer)
    max_confidence = Column(Float)
    min_confidence = Column(Float)
    avg_confidence = Column(Float)
    processing_time = Column(Float, nullable=True)  # 处理时间（秒）
    created_at = Column(DateTime, default=datetime.now)
    task = relationship("Task", back_populates="statistics")

class UserStatistics(Base):
    __tablename__ = "user_statistics"
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    total_tasks = Column(Integer, default=0)
    completed_tasks = Column(Integer, default=0)
    failed_tasks = Column(Integer, default=0)
    total_images = Column(Integer, default=0)
    total_videos = Column(Integer, default=0)
    total_pedestrians = Column(Integer, default=0)
    total_vehicles = Column(Integer, default=0)
    last_updated = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    user = relationship("User", back_populates="statistics")
